
package cn.hpclub.server.service.visit;

import cn.hpclub.server.util.InterfaceUtil;
import cn.hpclub.server.util.ObjectUtil;
import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.List;
import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @FileName: VisitService.java
 * @Author zhl
 * @Description:基础拜访Service
 * @Date 2016年6月27日 下午2:38:57
 * @CopyRight ZTE Corporation
 */
public class VisitService {

    private static final Logger    logger  = LoggerFactory.getLogger(VisitService.class);
    public static VisitService service = new VisitService();

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        String sqlExceptSelect = " FROM  " +
                "visit_info where 1=1  ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }
        sqlExceptSelect += " order by create_time desc ";
        return sqlExceptSelect;
    }
    private String buildQueryForCount(Entry<String, String> item){
        if("start_time".equalsIgnoreCase(item.getKey())){
            String start_time = "'" + item.getValue().replaceAll(" ", "") + " 00:00:00'";
            return " and visit_time >=" + start_time + " ";
        }
        if("end_time".equalsIgnoreCase(item.getKey())){
            String end_time = "'" + item.getValue().replaceAll(" ", "") + " 23:59:59'";
            return " and visit_time <=" + end_time + " ";
        }
        if("city_id".equalsIgnoreCase(item.getKey())){
            String city_id = "" + item.getValue().replaceAll(" ", "") + "";
            return " and city_id =" + city_id + " ";
        }
        return " and " + item.getKey() + " like '%" + item.getValue() + "%'";
    }

    /**
     * @describe 获取基础拜访列表的excel文件
     * @param searchInfo 查询条件参数列表
     * @return SXSSFWorkbook
     */
    public SXSSFWorkbook getExcel(ConcurrentHashMap<String, String> searchInfo){
        SXSSFWorkbook workbook = null;
                String sql = " SELECT * FROM visit_info WHERE 1=1 ";
        for(Entry<String, String> item : searchInfo.entrySet()){
            sql += buildQueryForCount(item);
        }
        sql += " ORDER BY create_time DESC ";
                List<Record> seq = Db.find(sql);
        String[] excelHeaders = new String[]{"姓名","手机号","工号","地市","区县","渠道","拜访集团编码","集团名称",
                "拜访时间","是否重要领导","是否一般领导","是否重要联系人","是否信息化联系人","是否首席代表","定位位置","备注"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"cm_name","cm_phone","job_number","city","district","channel","group_number","group_name",
                "visit_time","matter_leader","common_leader","matter_contact","info_contact","chief","location","remark"};

        for (int i = 0; i < seq.size(); i++) {
            Record record = seq.get(i);
            record.set("matter_leader",record.getInt("matter_leader")==1 ? "是" : "否");
            record.set("common_leader", record.getInt("common_leader")==1 ? "是" : "否");
            record.set("matter_contact",record.getInt("matter_contact")==1 ? "是" : "否");
            record.set("info_contact",record.getInt("info_contact")==1 ? "是" : "否");
            String chief = record.getStr("chief")!=null?record.getStr("chief"):"";
            record.set("chief","chief".equals(chief) ? "是" : "否");
        }
        try {
            exportUtils.exportExcel(keys,seq,"yyyy-MM-dd HH:ss:mm ",1);//此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"+e.getMessage());
        } catch (IOException ex) {
            logger.error("VisitService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"+ex.getMessage());
        }
        return workbook;
    }

    /**
     * @describe 获取基础拜访统计数据
     * @param searchInfo 参数列表
     * @return List<Record> 查询所得数据list
     */
    public List<Record> getVisitCount(ConcurrentHashMap<String, String> searchInfo){
        String selectSql = " SELECT count(*) as group_count,s.city_id as city_id,s.city as city,sum(s.visit_count) as visit_count";
        String sqlExceptSelect = " FROM (SELECT city_id,city,group_number,count(id) as visit_count FROM " +
                "visit_info where 1=1  ";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQueryForCount(item);
        }

        sqlExceptSelect += "  GROUP BY city_id,group_number) s  GROUP BY s.city_id ";

        String sql = selectSql + sqlExceptSelect;
        List<Record> seq = Db.find(sql);
        return seq;
    }


}
